package database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.Iterator;
import java.util.List;
import com.managment.data.*;

public class DatabaseConnector {
	private  Connection connection = null;
	
	public DatabaseConnector(){
		try{
			String username = "intelligentHouse";
			String password = "inhabitant";
			String url = "jdbc:mysql://localhost/IntelligentHouse";
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			connection = DriverManager.getConnection(url, username, password);
			System.out.println("Connected");
		}catch(ClassNotFoundException e){
			e.printStackTrace();
		}catch(IllegalAccessException e){
			e.printStackTrace();
		}catch(InstantiationException e){
			e.printStackTrace();
		}catch(SQLException e){
			e.printStackTrace();
		}
	}
	
	public boolean createProfil(String name){
		if(checkIfProfilExists(name)){
			return false;
		}
		int count = 0;
		try{
			String query = "Insert into Profils (name) values (\'"+name + "\')";
			count = executeUpdate(query);
		}catch(SQLException e){
			e.printStackTrace();
		}
		return count > 0;
	}

	private int executeUpdate(String query) throws SQLException {
		Statement statement = connection.createStatement();
		return statement.executeUpdate(query);
	}
	
	private boolean checkIfProfilExists(String name){
		String query = "Select 1 from Profils where name = \'" + name + "\'";
		boolean result = false;
		try {
			ResultSet rs = executeQuery(query);
			result = rs.next();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	private ResultSet executeQuery(String query) throws SQLException {
		Statement statement = connection.createStatement();
		ResultSet rs = statement.executeQuery(query);
		return rs;
	}
	
	public boolean dropProfil(String name){
		int count = 0;
		String query = "Delete from Profils where name =  \'"+name + "\'";
		try{
			count = executeUpdate(query);
		}catch(SQLException e){
			e.printStackTrace();
		}
		return count > 0;
	}
	
	public boolean addTaskToProfile(String name, Task task){
		if(!task.validateTask()){
			return false;
		}
		int profileId = getIdOfProfil(name);
		if(profileId < 0){
			return false;
		}
		task.setProfilId(profileId);
		if(getIdOfTask(task)>=0){
			return false;
		}
		String query = "Insert into ProfilsProperities (media,eventTime,state,profilId)" +
				" values (\'"+task.getMedia().toString()+"\', \'"+task.getEventTime() +"\', \'"+task.getState()+"\', "+profileId +")";
		int count = 0;
		try{
			count = executeUpdate(query);
		}catch(SQLException e){
			e.printStackTrace();
			return false;
		}
		int taskId = getIdOfTask(task);
		task.setId(taskId);
		return count > 0;
	}

	private int getIdOfTask(Task task) {
		String query = "Select id from ProfilsProperities where " +
				"media = \'"+task.getMedia().toString()+"\' and " +
				"eventTime = \'"+task.getEventTime() +"\' and " +
				"state = \'" + task.getState() + "\' and " +
				"profilId = "+task.getProfilId();
		int newId = -1;
		try{
			ResultSet rs = executeQuery(query);
			if(rs.next()){
				newId = rs.getInt(1);
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
		return newId;
	}
	
	public List<String> getProfilesNames(){
		String query = "Select name from Profils";
		List<String> list = new ArrayList<String>();
		try{
			ResultSet rs = executeQuery(query);
			while(rs.next()){
				list.add(rs.getString("name"));
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
		return list;
	}
	
	private int getIdOfProfil(String name){
		String query = "Select id from Profils where name = \'"+name+"\'";
		int id = -1;
		try {
			ResultSet rs = executeQuery(query);
			rs.next();
			id = rs.getInt("id");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return id;
	}
	
	public boolean deleteTask(Task task){
		String query = "Delete from ProfilsProperities where id = "+task.getId();
		int count = 0;
		try{
			count = executeUpdate(query);
		}catch(SQLException e){
			e.printStackTrace();
		}
		return count > 0;
	}
	
	public boolean deleteProfil(String name){
		String query = "Delete from Profils where name = \'" + name + "\'";
		int count = 0;
		try{
			count = executeUpdate(query);
		}catch(SQLException e){
			e.printStackTrace();
		}
		return count > 0;
	}
	
	public boolean addProfil(Profile profil){
		if(createProfil(profil.getName())){
			for(Iterator<Task> it = profil.getTasks().iterator();it.hasNext();){
				if(!addTaskToProfile(profil.getName(), it.next())){
					deleteProfil(profil.getName());
					return false;
				}
			}
			return true;
		}
		return false;
	}
	
	public List<Profile> getProfils(){
		List<Profile> profils = new ArrayList<Profile>();
		String query = "Select id, name from Profils";
		ResultSet rs = null;
		try {
			rs = executeQuery(query);
			while(rs.next()){
				Profile prof = new Profile();
				int profilId = rs.getInt("id");
				String name = rs.getString("name");
				prof.setName(name);
				query = "Select * from ProfilsProperities where profilId = " + profilId;
				ResultSet taskRS = executeQuery(query);
				while(taskRS.next()){
					Task task = new Task(Media.valueOf(taskRS.getString("media")), taskRS.getTime("eventTime"), taskRS.getString("state"));
					task.setId(taskRS.getInt("id"));
					task.setProfilId(profilId);
					prof.addTask(task);
				}
				profils.add(prof);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return profils;
	}
		
	public static void main(String [] args){
		DatabaseConnector dc = new DatabaseConnector();
		System.out.println(dc.createProfil("testowy"));
		
		Calendar cal2 = GregorianCalendar.getInstance();
		cal2.set(Calendar.HOUR_OF_DAY, 18);
		cal2.set(Calendar.MINUTE, 50);
		cal2.set(Calendar.SECOND, 0);
		Calendar cal = GregorianCalendar.getInstance();
		cal.set(Calendar.HOUR_OF_DAY, 1);
		cal.set(Calendar.MINUTE, 45);
		cal.set(Calendar.SECOND, 0);
		System.out.println(dc.addTaskToProfile("testowy", 
				new Task(Media.KITCHENLIGHT, new Time(cal.getTimeInMillis()),"on")));
		System.out.println(Media.valueOf("ROOMLIGHT"));
		System.out.println(dc.createProfil("testowy2"));
		System.out.println(dc.addTaskToProfile("testowy2", 
				new Task(Media.KITCHENLIGHT, new Time(cal.getTimeInMillis()),"on")));
		List<String> l = dc.getProfilesNames();
		for(Iterator<String> it = l.iterator(); it.hasNext();){
			System.out.println(it.next());
		}
	}
}
